package com.cd2cd.code_app.util;


import org.apache.commons.io.IOUtils;
import org.apache.ibatis.jdbc.ScriptRunner;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DBUtil {

	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public static List<Map<String, Object>> exeQuerySql(String sql, String host, int port, String user, String password, String db) throws SQLException {
		return exeSql(sql, host, port, user, password, db, false);
	}

	public static List<Map<String, Object>> exeSql(String sql, String host, int port, String user, String password, String db, boolean update) throws SQLException {
		
		String url = "jdbc:mysql://"+host+":"+port+"/" + db+"?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true";
		
		List<Map<String, Object>> tabData = new ArrayList<Map<String, Object>>();
		Connection mConnection = null;
		Statement mStatement = null;
		ResultSet mResultSet = null;

		try {
			mConnection = DriverManager.getConnection(url, user, password);
			
			mStatement = mConnection.createStatement();
			mResultSet = null;
			if(update) {
				mStatement.executeUpdate(sql);
			} else {
				mResultSet = mStatement.executeQuery(sql);


				ResultSetMetaData rsmd = mResultSet.getMetaData();
				int count = rsmd.getColumnCount();

				String[] columns = new String[count];
				for (int i = 0; i < count; i++) {
					String cName = rsmd.getColumnLabel(i + 1);

					columns[i] = cName;
				}

				while (mResultSet.next()) {

					Map<String, Object> row = new HashMap<String, Object>();
					for (String c : columns) {
						row.put(c, mResultSet.getObject(c));
					}
					tabData.add(row);
				}
			}
		} finally  {
			
			if( mConnection != null ) {
				try {
					mConnection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if( mStatement != null ) {
				try {
					mStatement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if( mResultSet != null ) {
				try {
					mResultSet.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
		}
		
		return tabData;
	}

	public static boolean hasDatabase(String dbName, String host, int port, String user, String password) {

		String sql = "select 1";
		try {
			exeQuerySql(sql, host, port, user, password, dbName);
			return true;
		} catch (SQLException e) {
			String msg = e.getMessage();
			System.out.println(msg);
			if(msg.indexOf("Unknown database") > -1) {
				return false;
			}
		}
		return true;
	}

	public static boolean createDatabase(String dbName, String host, int port, String user, String password) {
		String sql = "CREATE DATABASE IF NOT EXISTS "+dbName+" DEFAULT CHARSET utf8 COLLATE utf8_general_ci;";
		try {
			exeSql(sql, host, port, user, password, "", true);
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	public static boolean initDatabaseData(String dbName, String host, int port, String user, String password) {

		String jarFile = FileUtil.getPath()+FileUtil.service_path;
		InputStream input = FileUtil.loadResourceFromJarFile(jarFile, "BOOT-INF/classes/sql/mysql_schema_data.sql");

		Connection mConnection = null;
		Statement mStatement = null;
		ResultSet mResultSet = null;

		try {
			String sql = IOUtils.toString(input, "utf-8");

			List<String> blocks = new ArrayList<>();
			StringBuilder sqlStr = new StringBuilder();
			String[] lines = sql.split("\n");
			for(String line: lines) {
				if(line.startsWith("LOCK") ||
						line.startsWith("#")
				) {
					continue;
				}

				if(line.startsWith("/*!")) {
					line = line.substring(line.indexOf(" "), line.lastIndexOf(" ")) + ";";
				}

				sqlStr.append(line).append("\n");

				if(line.endsWith(";")) {
//					System.out.println(sqlStr);
					blocks.add(sqlStr.toString());
					sqlStr.delete(0, sqlStr.length());
				}
			}

			String url = "jdbc:mysql://"+host+":"+port+"/" + dbName+"?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull";
			mConnection = DriverManager.getConnection(url, user, password);
			mStatement = mConnection.createStatement();
			mResultSet = null;

			for (String bb: blocks) {

				System.out.println(bb);
				mStatement.execute(bb);
			}

			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally  {

			if( mStatement != null ) {
				try {
					mStatement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

			if( mConnection != null ) {
				try {
					mConnection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

			if( mResultSet != null ) {
				try {
					mResultSet.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

		}
	}

	/**
	 * 更新数据库脚本更新
	 */
	public static void updateDbSql(String dbName, String host, int port, String user, String password) {
		String jarFile = FileUtil.getPath()+FileUtil.service_path;
		InputStream input = FileUtil.loadResourceFromJarFile(jarFile, "BOOT-INF/classes/sql/update-schema.sql");
		try {
			String url = "jdbc:mysql://"+host+":"+port+"/" + dbName+"?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull";
			Connection mConnection = DriverManager.getConnection(url, user, password);
			ScriptRunner runner = new ScriptRunner(mConnection);
			runner.setAutoCommit(true);

			try {
					runner.setFullLineDelimiter(false);
					runner.setDelimiter(";;");//语句结束符号设置
					runner.setLogWriter(null);//日志数据输出，这样就不会输出过程
					runner.setSendFullScript(false);
					runner.setAutoCommit(true);
					runner.setStopOnError(true);
					runner.runScript(new InputStreamReader(input, "utf8"));
			} catch (Exception e) {
				e.printStackTrace();
			}
			mConnection.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) throws Exception {
		// List<Map<String, Object>> list = 
//		exeQuerySql("show table status;", "localhost", 3306, "root", "root", "test");
		


//		hasDatabase("auto_code","localhost", 3306, "root", "root");
//		createDatabase("ccccccccc000000001","localhost", 3306, "root", "root");
//		initDatabaseData("ccccccccc000000001","localhost", 3306, "root", "root");

//		String sql = IOUtils.toString(new FileInputStream(new File("/Volumes/data/code-sources/java-source/code_2_code/cd2cd/code_main/src/main/resources/sql/update-schema.sql")), "utf-8");

		// 192.168.31.20", 3306, "root", "123456", "test"

		updateDbSql("test", "192.168.31.20", 3306, "root", "123456");

	}

}
